﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.IO.Packaging;
using System.IO;

using Ins.Office.Common;
using Ins.Packaging;

namespace Ins.Office
{

    /// <summary>
    /// Provides functionality to write data from a dataset to an Excel spreadsheet.
    /// </summary>
    public class XLDataSetSheetWriter : IXLSheetWriter<DataSet>
    {
        private IDictionary<string, int> _sharedStringsDict;
        private string _outputDir = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);

        /// <summary>
        /// Initializes a new instance of the XLDataSetSheetWriter class.
        /// </summary>
        /// <param name="data">The DataSet containing the table to be written to the spreadsheet.</param>
        /// <param name="sheetCodeName">The spreadsheet internal name.</param>
        /// <param name="workbookPath">The full path of the Excel workbook.</param>
        /// <param name="tableName">The name of the table written in the spreadsheet.</param>
        public XLDataSetSheetWriter(DataSet data, string sheetCodeName, string workbookPath, string tableName)
        {
            this.Data = data;
            this.SheetCodeName = sheetCodeName;
            this.WorkbookPath = workbookPath;
            this.TableName = tableName;
        }

        /// <summary>
        /// Gets the DataSet object containing the data table to be written to the spreadsheet.
        /// </summary>
        public DataSet Data { get; private set; }

        /// <summary>
        /// Gets the internal sheet name.
        /// </summary>
        public string SheetCodeName { get; private set; }

        /// <summary>
        /// Gets the full path of the workbook containing the spreadsheet.
        /// </summary>
        public string WorkbookPath { get; private set; }

        /// <summary>
        /// Gets the name of the DataTable object containing the data to be written.
        /// </summary>
        public string TableName { get; private set; }

        /// <summary>
        /// Writes the data from the dataset to the spreadsheet. 
        /// </summary>
        public void Write()
        {
            _sharedStringsDict = XLUtil.GetSharedStringsDictionary(WorkbookPath);
            using (Package pkg = Package.Open(WorkbookPath, FileMode.Open))
            {
                PackageSettings settings = new XLPackageSettings();
                Pack p = new Pack(pkg, settings);
                PackagePart documentPart = p.GetDocumentPart();
                string sheetPartPath = "/xl/worksheets/" + SheetCodeName.ToLower() + ".xml";
                string sheetFileName = Path.Combine(new string[] { _outputDir, Path.GetRandomFileName() + "." + SheetCodeName + ".xml" });
                string sharedStringsPartPath = "/xl/sharedStrings.xml";
                string sharedStringsFileName = Path.Combine(new string[] { _outputDir, Path.GetRandomFileName() + ".sharedStrings.xml" });

                using (XmlWriter writer = XmlTextWriter.Create(sheetFileName))
                {
                    XLUtil.WriteSheetStart(writer);

                    WriteSheetData(writer);

                    XLUtil.WriteSheetEnd(writer);
                }

                XLUtil.WriteSharedStrings(_sharedStringsDict, sharedStringsFileName);

                p.ReplacePart(documentPart, XLPackageSettings.SharedStringsRelationType, sharedStringsPartPath, XLPackageSettings.SharedStringsContentType, sharedStringsFileName);
                p.ReplacePart(documentPart, XLPackageSettings.WorksheetRelationType, sheetPartPath, XLPackageSettings.WorksheetContentType, sheetFileName);
            }
        }

        /// <summary>
        /// Writes the column names in a row in the spreadsheet.
        /// </summary>
        /// <param name="writer">The XmlWriter object associated with the spreadsheet.</param>
        /// <param name="columns">The column collection.</param>
        /// <param name="index">The index value used when writing string values in the shared strings dictionary.</param>
        private void WriteHeader(XmlWriter writer, DataColumnCollection columns, ref int index)
        {
            string typeName = "System.String";
            string columnName;
            writer.WriteStartElement("row");
            foreach (DataColumn col in columns)
            {
                columnName = col.ColumnName;
                writer.WriteStartElement("c");
                XLUtil.WriteValue(writer, typeName, columnName, _sharedStringsDict, ref index);
                writer.WriteEndElement(); //c
            }
            writer.WriteEndElement(); //row
        }

        /// <summary>
        /// Writes the sheetData element and its content to the spreadsheet.
        /// </summary>
        /// <param name="writer">The XmlWriter object associated with the spreadsheet.</param>
        /// <remarks>
        /// The sheetData element contains row elements. The rows contain column ("c") elements which contain value elements ("v").
        /// </remarks>
        private void WriteSheetData(XmlWriter writer)
        {
            if (!Data.Tables.Contains(TableName))
                throw new Exception(string.Format("Table {0} does not exist.", TableName));
            writer.WriteStartElement("sheetData");
            string columnName;
            string typeName;
            string value;
            int index = _sharedStringsDict.Count;
            DataTable table = Data.Tables[TableName];
            WriteHeader(writer, table.Columns, ref index);
            foreach(DataRow row in table.Rows)
            {
                writer.WriteStartElement("row");
                foreach (DataColumn col in table.Columns)
                {
                    columnName = col.ColumnName;
                    typeName = col.DataType.ToString();
                    value = row[columnName].ToString();
                    writer.WriteStartElement("c");
                    if (!string.IsNullOrEmpty(value))
                    {
                        XLUtil.WriteValue(writer, typeName, value, _sharedStringsDict, ref index);
                    }
                    else
                    {
                        writer.WriteStartElement("v");
                        writer.WriteEndElement(); //v
                    }
                    writer.WriteEndElement(); //c
                } //foreach row in schema
                writer.WriteEndElement(); //row
            } //while Data.Read
            writer.WriteEndElement(); //sheetData
        } //WriteSheetData

    }
}
